﻿CREATE TABLE [dbo].[SiteInfo](
	[SiteID] [uniqueidentifier] NOT NULL,
	[WebID] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](255) NOT NULL,
	[Url] [nvarchar](255) NOT NULL,
	[IndexDate] [datetime] NULL,
	[SiteCollectionTitle] [nvarchar](255) NOT NULL,
	[SiteCollectionUrl] [nvarchar](255) NOT NULL,
	[WebApplicationId] [uniqueidentifier] NOT NULL,
	[FarmId] [uniqueidentifier] NOT NULL
 CONSTRAINT [PK_SiteInfo] PRIMARY KEY CLUSTERED 
(
	[SiteID] ASC,
	[WebID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetSiteInfo]
	@FarmId UniqueIdentifier,
	@WebApplicationID UniqueIdentifier,
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier
AS
BEGIN
	select
		Title, Url, SiteCollectionTitle, SiteCollectionUrl, IndexDate 
	from
		SiteInfo 
	where 
		FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID
END
GO
-- =============================================
CREATE PROCEDURE [dbo].[UpdateSiteInfo]
	@FarmId UniqueIdentifier,
	@WebApplicationID UniqueIdentifier,
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier,
	@Title nvarchar(255),
	@Url nvarchar(255),
	@SiteCollectionTitle nvarchar(255),
	@SiteCollectionUrl nvarchar(255),
	@IndexDate DateTime
AS
BEGIN
if exists (select Title from SiteInfo where FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID)
begin
	update SiteInfo set Title = @Title, Url=@Url, SiteCollectionTitle=@SiteCollectionTitle, SiteCollectionUrl=@SiteCollectionUrl, IndexDate=@IndexDate 
	where FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID
end else
begin
	insert into SiteInfo 
		(FarmId, WebApplicationId, SiteID, WebID, Title, Url, SiteCollectionTitle, SiteCollectionUrl, IndexDate)
	values
		(@FarmId, @WebApplicationId, @SiteID, @WebID, @Title, @Url, @SiteCollectionTitle, @SiteCollectionUrl, @IndexDate)
end

END